#region Copyright (c) 2006-2012 Widgetsphere LLC, All Rights Reserved
//--------------------------------------------------------------------- *
//                          Widgetsphere  LLC                           *
//             Copyright (c) 2006-2012 All Rights reserved              *
//                                                                      *
//                                                                      *
//This file and its contents are protected by United States and         *
//International copyright laws.  Unauthorized reproduction and/or       *
//distribution of all or any portion of the code contained herein       *
//is strictly prohibited and will result in severe civil and criminal   *
//penalties.  Any violations of this copyright will be prosecuted       *
//to the fullest extent possible under law.                             *
//                                                                      *
//THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *
//TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *
//TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
//CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *
//THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF WIDGETSPHERE LLC   *
//                                                                      *
//UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *
//PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *
//SUBSTANTIALLY THE SAME, FUNCTIONALITY AS ANY WIDGETSPHERE PRODUCT.    *
//                                                                      *
//THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *
//CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF WIDGETSPHERE,      *
//INC.  THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO       *
//INSURE ITS CONFIDENTIALITY.                                           *
//                                                                      *
//THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *
//PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *
//EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *
//THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *
//SOURCE CODE CONTAINED HEREIN.                                         *
//                                                                      *
//THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *
//--------------------------------------------------------------------- *
#endregion
using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using Widgetsphere.Generator;
using Widgetsphere.Generator.Models;
using System.Collections;
using Widgetsphere.Generator.Common.Util;
using Widgetsphere.Generator.ProjectItemGenerators;

namespace Widgetsphere.Generator.Installer.ProjectItemGenerators.DatabaseSchema
{
	class CreateSchemaTemplate : BaseDbScriptTemplate
	{
		private StringBuilder sb = new StringBuilder();

		#region Constructors
		public CreateSchemaTemplate(ModelRoot model)
		{
			_model = model;
		}
		#endregion

		#region BaseClassTemplate overrides
		public override string FileContent
		{
			get
			{
				GenerateContent();
				return sb.ToString();
			}
		}

		public override string FileName
		{
			get
			{
				return string.Format("CreateSchema.sql");
			}
		}
		#endregion

		#region GenerateContent
		private void GenerateContent()
		{
			try
			{
				sb = new StringBuilder();
				sb.AppendLine("--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.");
				sb.AppendLine("--Data Schema For Version " + _model.Version);
				sb.AppendLine("--Generated on " + DateTime.Now.ToString("yyy-MM-dd HH:mm:ss"));
				sb.AppendLine();

				this.AppendCreateSchema();
				this.AppendCreateTable();
				this.AppendAuditTracking();
				this.AppendCreateAudit();
				this.AppendCreatePrimaryKey();
				this.AppendCreateUniqueKey();
				this.AppendCreateForeignKey();
				this.AppendCreateIndexes();
				this.AppendCreateDefaults();
				this.AppendFixNulls();
				this.AppendClearSP();
			}
			catch (Exception ex)
			{
				throw;
			}
		}

		#region Append CreateTable

		private void AppendCreateSchema()
		{
			List<string> list = new List<string>();

			//Tables
			foreach (var item in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			{
				string s = item.GetSQLSchema().ToLower();
				if (!list.Contains(s) && s != "dbo")
				{
					list.Add(s);
				}
			}

			//Views
			foreach (var item in (from x in _model.Database.CustomViews where x.Generated orderby x.Name select x))
			{
				string s = item.GetSQLSchema().ToLower();
				if (!list.Contains(s) && s != "dbo")
				{
					list.Add(s);
				}
			}

			//Stored Procedures
			foreach (var item in (from x in _model.Database.CustomStoredProcedures where x.Generated orderby x.Name select x))
			{
				string s = item.GetSQLSchema().ToLower();
				if (!list.Contains(s) && s != "dbo")
				{
					list.Add(s);
				}
			}

			if (list.Count > 0)
			{
				sb.AppendLine("--CREATE DATABASE SCHEMAS");
				foreach (string s in list)
				{
					sb.AppendLine("if not exists(select * from sys.schemas where name = '" + s + "')");
					sb.AppendLine("exec('CREATE SCHEMA [" + s + "]')");
					sb.AppendLine("GO");
				}				
				sb.AppendLine();
			}

		}

		private void AppendCreateTable()
		{
			foreach (Table table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			{
				sb.AppendLine("--CREATE TABLE [" + table.DatabaseName + "]");
				sb.AppendLine(SQLGeneration.GetSQLCreateTable(_model, table));
				sb.AppendLine("GO");
				sb.AppendLine();
			}

			////Drop all descriptions
			//foreach (Table table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			//{
			//  sb.AppendLine("--DROP ALL COLUMN DESCRIPTIONS FOR TABLE [" + table.DatabaseName + "]");
			//  foreach (Column column in table.GetColumns())
			//  {
			//    sb.AppendLine("EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'" + table.DatabaseName + "', @level2type=N'COLUMN',@level2name=N'" + column.DatabaseName + "'");
			//  }
			//  sb.AppendLine();
			//}

			////Add all descriptions
			//foreach (Table table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			//{
			//  sb.AppendLine("--ADD ALL COLUMN DESCRIPTIONS FOR TABLE [" + table.DatabaseName + "]");
			//  foreach (Column column in table.GetColumns())
			//  {
			//    if (!string.IsNullOrEmpty(column.Description))
			//    {
			//      sb.AppendLine("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'" + column.Description + "', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'" + table.DatabaseName + "', @level2type=N'COLUMN',@level2name=N'" + column.DatabaseName + "'");
			//    }
			//  }
			//  sb.AppendLine();
			//}

		}
		#endregion

		#region Append AuditTracking
		private void AppendAuditTracking()
		{
			foreach (var table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			{
				if (table.AllowAuditTracking)
				{
					sb.AppendLine("--CREATE AUDIT TABLE FOR [" + table.DatabaseName + "]");
					sb.AppendLine(SQLGeneration.GetSQLCreateAuditTable(_model, table));
					sb.AppendLine("GO");
					sb.AppendLine();

					sb.AppendLine("--ENSURE ALL COLUMNS ARE CORRECT TYPE");
					string tableName = "__AUDIT__" + table.DatabaseName;

					foreach (var column in table.GetColumns().Where(x => x.Generated).OrderBy(x => x.Name))
					{
						if (!(column.DataType == System.Data.SqlDbType.Text || column.DataType == System.Data.SqlDbType.NText || column.DataType == System.Data.SqlDbType.Image))
						{
							//Now add columns if they do not exist
							sb.AppendLine("if not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + column.DatabaseName + "' and o.name = '" + tableName + "')");
							sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] ADD [" + column.DatabaseName + "] " + column.DatabaseType + " NULL");
							sb.AppendLine("GO");
							sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] ALTER COLUMN [" + column.DatabaseName + "] " + column.DatabaseType + " NULL");
							sb.AppendLine("GO");
							sb.AppendLine();
						}
					}

					if (table.AllowModifiedAudit)
					{
						sb.AppendLine("if not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.ModifiedByDatabaseName + "' and o.name = '" + tableName + "')");
						sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] ADD [" + _model.Database.ModifiedByDatabaseName + "] [Varchar] (50) NULL");
						sb.AppendLine("GO");
						sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] ALTER COLUMN [" + _model.Database.ModifiedByDatabaseName + "] [Varchar] (50) NULL");
						sb.AppendLine("GO");
						sb.AppendLine();
					}

				}
			}
		}
		#endregion

		#region Append Primary Key

		private void AppendCreatePrimaryKey()
		{
			//Rename existing PK if they exist
			sb.AppendLine("--RENAME EXISTING PRIMARY KEYS IF NECESSARY");
			foreach (var table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			{
				sb.AppendLine("DECLARE @pkfix" + table.PascalName + " varchar(500)");
				sb.AppendLine("SET @pkfix" + table.PascalName + " = (SELECT top 1 i.name AS IndexName FROM sys.indexes AS i WHERE i.is_primary_key = 1 AND OBJECT_NAME(i.OBJECT_ID) = '" + table.DatabaseName + "')");
				sb.AppendLine("if @pkfix" + table.PascalName + " <> '' and (BINARY_CHECKSUM(@pkfix" + table.PascalName + ") <> BINARY_CHECKSUM('PK_" + table.DatabaseName.ToUpper() + "')) exec('sp_rename '''+@pkfix" + table.PascalName + "+''', ''PK_" + table.DatabaseName.ToUpper() + "''')");
			}
			sb.AppendLine("GO");
			sb.AppendLine();

			//Create PK
			foreach (var table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			{
				string tableName = Globals.GetTableDatabaseName(_model, table);
				string indexName = "PK_" + tableName;
				indexName = indexName.ToUpper();

				if (table.EnforcePrimaryKey)
				{
					sb.Append(Globals.GetPrimaryKeyCreateScript(table, _model));
				}
				else
				{
					//Drop the PK if necessary
					sb.AppendLine("--DROP PRIMARY KEY FOR TABLE [" + tableName + "]");
					sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'" + indexName + "'))");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP CONSTRAINT [" + indexName + "]");
					sb.AppendLine("GO");
					sb.AppendLine();
				}

				//Setup for Audits
				tableName = "__AUDIT__" + Globals.GetTableDatabaseName(_model, table);
				indexName = "PK_" + tableName.ToUpper();

				//If there is an audit table then make its surrogate key PK clustered
				if (table.AllowAuditTracking)
				{
					sb.AppendLine("--PRIMARY KEY FOR TABLE [" + tableName + "]");
					sb.AppendLine("if not exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'PK')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] WITH NOCHECK ADD");
					sb.Append("CONSTRAINT [" + indexName + "] PRIMARY KEY CLUSTERED ([__rowid])");

					//No file groups for Azure
					if (_model.SQLServerType != SQLServerTypeConstants.SQLAzure)
					{
						sb.Append(" ON [PRIMARY]");
					}

					sb.AppendLine();
					sb.AppendLine("GO");
					sb.AppendLine();
				}
				else
				{
					//Drop the PK if necessary
					sb.AppendLine("--DROP PRIMARY KEY FOR TABLE [" + tableName + "]");
					sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'" + indexName + "'))");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] DROP CONSTRAINT [" + indexName + "]");
					sb.AppendLine("GO");
					sb.AppendLine();
				}

			}
		}

		#endregion

		#region Append Foreign Keys

		private void AppendCreateForeignKey()
		{
			foreach (var table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			{
				string tableName = Globals.GetTableDatabaseName(_model, table);
				var childRoleRelations = table.ChildRoleRelations;
				if (childRoleRelations.Count > 0)
				{
					for (int ii = 0; ii < childRoleRelations.Count; ii++)
					{
						var relation = childRoleRelations[ii] as Relation;
						if (relation.Enforce)
						{
							var childTable = relation.ChildTable;
							var parentTable = relation.ParentTable;
							if (childTable.Generated && parentTable.Generated)
							{
								var indexName = "FK_" + relation.DatabaseRoleName + "_" + Globals.GetTableDatabaseName(_model, childTable) + "_" + Globals.GetTableDatabaseName(_model, parentTable) + "";
								indexName = indexName.ToUpper();
								sb.AppendLine("--FOREIGN KEY RELATIONSHIP [" + parentTable.DatabaseName + "] -> [" + childTable.DatabaseName + "] (" + GetFieldNames(relation) + ")");
								sb.AppendLine("if not exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'F')");
								sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] ADD ");
								sb.AppendLine("CONSTRAINT [" + indexName + "] FOREIGN KEY ");
								sb.AppendLine("(");
								this.AppendChildTableColumns(relation);
								sb.AppendLine(") REFERENCES [" + parentTable.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(_model, parentTable) + "] (");
								this.AppendParentTableColumns(relation);
								sb.AppendLine(")");
								sb.AppendLine("GO");
								sb.AppendLine();
							}
						}
					}
				}
			}
		}

		private string GetFieldNames(Relation relation)
		{
			var retval = new StringBuilder();
			for (int kk = 0; kk < relation.ColumnRelationships.Count; kk++)
			{
				var columnRelationship = relation.ColumnRelationships[kk];
				var parentColumn = columnRelationship.ParentColumn;
				var childColumn = columnRelationship.ChildColumn;
				var parentTable = parentColumn.ParentTable;
				var childTable = childColumn.ParentTable;
				retval.Append("[" + parentTable.DatabaseName + "].[" + parentColumn.DatabaseName + "] -> ");
				retval.Append("[" + childTable.DatabaseName + "].[" + childColumn.DatabaseName + "]");
				if (kk < relation.ColumnRelationships.Count - 1)
				{
					retval.Append(", ");
				}
			}
			return retval.ToString();
		}

		private void AppendChildTableColumns(Relation relation)
		{
			//Sort the columns by PK/Unique first and then by name
			var columnList = new SortedDictionary<string, Column>();
			foreach (ColumnRelationship columnRelationship in relation.ColumnRelationships)
			{
				var key = string.Empty;
				if (columnRelationship.ParentColumn.PrimaryKey) key += "A";
				else if (columnRelationship.ParentColumn.IsUnique) key += "B";
				else key += "C";
				key += columnRelationship.ParentColumn.DatabaseName.ToLower();
				columnList.Add(key, columnRelationship.ChildColumn);
			}

			int index = 0;
			foreach (var column in columnList.Values)
			{
				sb.Append("	[" + column.DatabaseName + "]");
				if (index < relation.ColumnRelationships.Count - 1)
					sb.Append(",");
				sb.AppendLine();
				index++;
			}
		}

		private void AppendParentTableColumns(Relation relation)
		{
			var columnList = new SortedDictionary<string, Column>();
			foreach (ColumnRelationship columnRelationship in relation.ColumnRelationships)
			{
				var key = string.Empty;
				if (columnRelationship.ParentColumn.PrimaryKey) key += "A";
				else if (columnRelationship.ParentColumn.IsUnique) key += "B";
				else key += "C";
				key += columnRelationship.ParentColumn.DatabaseName.ToLower();
				columnList.Add(key, columnRelationship.ParentColumn);
			}

			int index = 0;
			foreach (var column in columnList.Values)
			{
				sb.Append("	[" + column.DatabaseName + "]");
				if (index < relation.ColumnRelationships.Count - 1)
					sb.Append(",");
				sb.AppendLine();
				index++;
			}
		}

		#endregion

		#region AppendCreateIndexes

		private void AppendCreateIndexes()
		{
			foreach (Table table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			{
				string tableName = Globals.GetTableDatabaseName(_model, table);
				foreach (Column column in table.GetColumns().Where(x => x.IsIndexed && !x.PrimaryKey && !x.IsUnique))
				{
					//Make sure that the index name is the same each time
					string indexName = "IDX_" + table.Name.Replace("-", "") + "_" + column.Name.Replace("-", "");
					indexName = indexName.ToUpper();

					sb.AppendLine("--INDEX FOR TABLE [" + ((Table)column.ParentTableRef.Object).DatabaseName + "] COLUMN [" + column.DatabaseName + "]");
					sb.AppendLine("if not exists(select * from sys.indexes where name = '" + indexName + "')");
					sb.Append("CREATE INDEX [" + indexName + "] ON [" + table.GetSQLSchema() + "].[" + tableName + "]([" + column.DatabaseName + "])");

					//No file groups on Azure
					if (_model.SQLServerType != SQLServerTypeConstants.SQLAzure)
					{
						sb.Append(" ON [PRIMARY]");
					}

					sb.AppendLine();

					sb.AppendLine("GO");
					sb.AppendLine();
				}
			}
		}

		#endregion

		#region AppendCreateUniqueKey

		private void AppendCreateUniqueKey()
		{
			foreach (Table table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			{
				string tableName = Globals.GetTableDatabaseName(_model, table);
				foreach (Reference reference in table.Columns)
				{
					//If this is a non-key column that is unqiue then create the SQL KEY
					Column column = (Column)reference.Object;
					if (column.IsUnique && !table.PrimaryKeyColumns.Contains(column))
					{
						//Make sure that the index name is the same each time
						string indexName = "IX_" + table.Name.Replace("-", "") + "_" + column.Name.Replace("-", "");
						indexName = indexName.ToUpper();
						
						sb.AppendLine("--UNIQUE COLUMN TABLE [" + tableName + "].[" + column.DatabaseName + "] (NON-PRIMARY KEY)");
						sb.AppendLine("if not exists(select * from sys.indexes where name = '" + indexName + "')");
						sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] ADD CONSTRAINT [" + indexName + "] UNIQUE ([" + column.DatabaseName + "]) ");
						sb.AppendLine("GO");
						sb.AppendLine();
					}
				}
			}
		}

		#endregion

		#region AppendCreateDefaults

		private void AppendCreateDefaults()
		{
			foreach (var table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			{
				//Add Defaults
				var tempsb = new StringBuilder();
				foreach (var column in table.GetColumns())
				{
					var defaultText = SQLGeneration.AppendColumnDefaultSQL(column);
					if (!string.IsNullOrEmpty(defaultText)) tempsb.Append(defaultText);
				}

				if (tempsb.ToString() != string.Empty)
				{
					sb.AppendLine("--BEGIN DEFAULTS FOR TABLE [" + table.DatabaseName + "]");
					sb.Append(tempsb.ToString());
					sb.AppendLine("--END DEFAULTS FOR TABLE [" + table.DatabaseName + "]");
					sb.AppendLine("GO");
					sb.AppendLine();
				}

			}
		}

		#endregion

		#region AppendClearSP

		private void AppendClearSP()
		{
			sb.AppendLine("--CLEAR ALL EXISTING GENERATED STORED PROCEDURES");
			sb.AppendLine("select '[' + s.name + '].[' + o.name + ']' as [text] ");
			sb.AppendLine("into #tmpDropSP");
			sb.AppendLine("from sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id ");
			sb.AppendLine("where o.name like '" + _model.GetStoredProcedurePrefix() + "_%' and type = 'P'");
			sb.AppendLine();
			sb.AppendLine("--LOOP AND REMOVE THESE CONSTRAINTS");
			sb.AppendLine("DECLARE @mycur CURSOR");
			sb.AppendLine("DECLARE @test VARCHAR(1000)");
			sb.AppendLine("SET @mycur = CURSOR");
			sb.AppendLine("FOR");
			sb.AppendLine("SELECT [text] FROM #tmpDropSP");
			sb.AppendLine("OPEN @mycur");
			sb.AppendLine("FETCH NEXT FROM @mycur INTO @test");
			sb.AppendLine("WHILE @@FETCH_STATUS = 0");
			sb.AppendLine("BEGIN");
			sb.AppendLine("exec(");
			sb.AppendLine("'");
			sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N''' + @test + ''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)");
			sb.AppendLine("drop procedure ' + @test + '");
			sb.AppendLine("')");
			sb.AppendLine("FETCH NEXT FROM @mycur INTO @test");
			sb.AppendLine("END");
			sb.AppendLine("DEALLOCATE @mycur");
			sb.AppendLine();
			sb.AppendLine("DROP TABLE #tmpDropSP");
			sb.AppendLine("GO");
			sb.AppendLine();
		}

		#endregion

		#region AppendCreateAudit

		private void AppendCreateAudit()
		{
			foreach (Table table in (from x in _model.Database.Tables where x.Generated orderby x.Name select x))
			{
				if (table.AllowCreateAudit)
				{
					sb.AppendLine("--APPEND AUDIT TRAIL CREATE FOR TABLE [" + table.DatabaseName + "]");
					sb.AppendLine("if not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.CreatedByColumnName + "' and o.name = '" + table.DatabaseName + "')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ADD [" + _model.Database.CreatedByColumnName + "] [Varchar] (50) NULL");
					string dfName = "DF__" + table.DatabaseName + "_" + _model.Database.CreatedDateColumnName;
					dfName = dfName.ToUpper();
					sb.AppendLine("if not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.CreatedDateColumnName + "' and o.name = '" + table.DatabaseName + "')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ADD [" + _model.Database.CreatedDateColumnName + "] [DateTime] CONSTRAINT [" + dfName + "] DEFAULT " + _model.GetSQLDefaultDate() + " NULL");
					sb.AppendLine();
				}
				else
				{
					sb.AppendLine("--REMOVE AUDIT TRAIL CREATE FOR TABLE [" + table.DatabaseName + "]");
					sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.CreatedByColumnName + "' and o.name = '" + table.DatabaseName + "')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP COLUMN [" + _model.Database.CreatedByColumnName + "]");
					string dfName = "DF__" + table.DatabaseName + "_" + _model.Database.CreatedDateColumnName;
					dfName = dfName.ToUpper();
					sb.AppendLine("if exists (select * from sys.objects where name = '" + dfName + "' and [type] = 'D')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP CONSTRAINT [" + dfName + "]");
					sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.CreatedDateColumnName + "' and o.name = '" + table.DatabaseName + "')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP COLUMN [" + _model.Database.CreatedDateColumnName + "]");
					sb.AppendLine();

				}

				if (table.AllowModifiedAudit)
				{
					sb.AppendLine("--APPEND AUDIT TRAIL MODIFY FOR TABLE [" + table.DatabaseName + "]");
					sb.AppendLine("if not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.ModifiedByColumnName + "' and o.name = '" + table.DatabaseName + "')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ADD [" + _model.Database.ModifiedByColumnName + "] [Varchar] (50) NULL");
					string dfName = "DF__" + table.DatabaseName + "_" + _model.Database.ModifiedDateColumnName;
					dfName = dfName.ToUpper();
					sb.AppendLine("if not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.ModifiedDateColumnName + "' and o.name = '" + table.DatabaseName + "')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ADD [" + _model.Database.ModifiedDateColumnName + "] [DateTime] CONSTRAINT [" + dfName + "] DEFAULT " + _model.GetSQLDefaultDate() + " NULL");
					sb.AppendLine();
				}
				else
				{
					sb.AppendLine("--REMOVE AUDIT TRAIL MODIFY FOR TABLE [" + table.DatabaseName + "]");
					sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.ModifiedByColumnName + "' and o.name = '" + table.DatabaseName + "')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP COLUMN [" + _model.Database.ModifiedByColumnName + "]");
					string dfName = "DF__" + table.DatabaseName + "_" + _model.Database.ModifiedDateColumnName;
					dfName = dfName.ToUpper();
					sb.AppendLine("if exists (select * from sys.objects where name = '" + dfName + "' and [type] = 'D')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP CONSTRAINT [" + dfName + "]");
					sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.ModifiedDateColumnName + "' and o.name = '" + table.DatabaseName + "')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP COLUMN [" + _model.Database.ModifiedDateColumnName + "]");
					sb.AppendLine();
				}

				if (table.AllowTimestamp)
				{
					sb.AppendLine("--APPEND AUDIT TRAIL TIMESTAMP FOR TABLE [" + table.DatabaseName + "]");
					sb.AppendLine("if not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.TimestampColumnName + "' and o.name = '" + table.DatabaseName + "')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ADD [" + _model.Database.TimestampColumnName + "] [timestamp] NOT NULL");
					sb.AppendLine();
				}
				else
				{
					sb.AppendLine("--REMOVE AUDIT TRAIL TIMESTAMP FOR TABLE [" + table.DatabaseName + "]");
					sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + _model.Database.TimestampColumnName + "' and o.name = '" + table.DatabaseName + "')");
					sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP COLUMN [" + _model.Database.TimestampColumnName + "]");
					sb.AppendLine();
				}

				sb.AppendLine("GO");
				sb.AppendLine();

			}
		}

		#endregion

		#region AppendFixNulls

		private void AppendFixNulls()
		{
			foreach (var t in _model.Database.Tables.Where(x => x.Generated).OrderBy(x => x.Name))
			{
				sb.AppendLine("--VERIFY COLUMNS ARE CORRECTLY NULLABLE FOR TABLE [" + t.DatabaseName + "]");
				foreach (var c in t.GetColumns().Where(x => !x.ComputedColumn))
				{
					if (!c.PrimaryKey && !string.IsNullOrEmpty(c.Default))
					{
						var dv = SQLGeneration.GetDetailSQLValue(c);
						if (!string.IsNullOrEmpty(dv))
						{
							sb.AppendLine("if not exists(select * from sys.objects where type_desc = 'DEFAULT_CONSTRAINT' and name = '" + SQLGeneration.GetDefaultValueConstraintName(c) + "')");
							sb.AppendLine("ALTER TABLE [" + t.GetSQLSchema() + "].[" + t.DatabaseName + "] ADD CONSTRAINT [" + SQLGeneration.GetDefaultValueConstraintName(c) + "] DEFAULT (" + dv + ") FOR [" + c.DatabaseName + "]");
						}
					}

					//Get the index for an indexed field (if one exists)
					var indexName = "IDX_" + t.Name.Replace("-", "") + "_" + c.Name.Replace("-", "");
					indexName = indexName.ToUpper();

					sb.AppendLine("if exists(select s.name as schemaname, o.name as tablename, c.name as columnname, c.is_nullable from sys.objects o inner join sys.columns c on o.object_id = c.object_id inner join sys.schemas s on o.schema_id = s.schema_id where s.name = '" + t.GetSQLSchema() + "' AND o.name = '" + t.DatabaseName + "' and c.name = '" + c.DatabaseName + "' and o.type = 'U' and c.is_nullable = " + (c.AllowNull ? "0" : "1") + ")");
					sb.AppendLine("BEGIN");

					if (c.IsIndexed && !c.PrimaryKey && !c.IsUnique)
					{
						sb.AppendLine("if exists(select * from sys.indexes where name = '" + indexName + "')");
						sb.AppendLine("	DROP INDEX [" + indexName + "] ON [" + t.GetSQLSchema() + "].[" + t.DatabaseName + "]");
					}
					
					sb.Append("ALTER TABLE [" + t.GetSQLSchema() + "].[" + t.DatabaseName + "] ALTER COLUMN [" + c.DatabaseName + "] " + c.DatabaseType + " " + (c.AllowNull ? "NULL" : "NOT NULL"));

					sb.AppendLine();

					if (c.IsIndexed && !c.PrimaryKey && !c.IsUnique)
					{
						sb.AppendLine("if not exists(select * from sys.indexes where name = '" + indexName + "')");
						sb.Append("CREATE INDEX [" + indexName + "] ON [" + t.GetSQLSchema() + "].[" + t.DatabaseName + "]([" + c.DatabaseName + "])");
					}

					sb.AppendLine("END");
				}
				sb.AppendLine("GO");
				sb.AppendLine();
			}
		}

		#endregion

		#region Methods


		#endregion

		#endregion
	}
}